package com.toppitch.site.controller;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.toppitch.site.data.CareerInterest;
import com.toppitch.site.data.HedgeFund;
import com.toppitch.site.data.JsonData;
import com.toppitch.site.data.Paging;
import com.toppitch.site.data.Student;
import com.toppitch.site.db.DBManager;
import com.toppitch.site.db.IGetFromResultSet;

@Controller
@RequestMapping("/")
public class ProfileController extends BaseController {

	@Autowired
	private DBManager dbmanager;

	@RequestMapping("profilelist")
	public String profileList(
			@RequestParam(value = "page", defaultValue = "1") String page,//
			@RequestParam(value = "keyWords", defaultValue = "") String keyWords,//
			@RequestParam(value = "schoolFilter1", defaultValue = "") String schoolFilter1,//
			@RequestParam(value = "schoolFilter2", defaultValue = "") String schoolFilter2,//
			@RequestParam(value = "schoolFilter3", defaultValue = "") String schoolFilter3,//
			@RequestParam(value = "schoolFilter4", defaultValue = "") String schoolFilter4,//
			@RequestParam(value = "schoolFilter5", defaultValue = "") String schoolFilter5,//
			@RequestParam(value = "schoolFilter6", defaultValue = "") String schoolFilter6,//
			@RequestParam(value = "industryFilter1", defaultValue = "") String industryFilter1,//
			@RequestParam(value = "industryFilter2", defaultValue = "") String industryFilter2,//
			@RequestParam(value = "industryFilter3", defaultValue = "") String industryFilter3,//
			@RequestParam(value = "industryFilter4", defaultValue = "") String industryFilter4,//
			@RequestParam(value = "industryFilter5", defaultValue = "") String industryFilter5,//
			@RequestParam(value = "industryFilter6", defaultValue = "") String industryFilter6,//
			@RequestParam(value = "industryFilter7", defaultValue = "") String industryFilter7,//
			@RequestParam(value = "industryFilter8", defaultValue = "") String industryFilter8,//
			@RequestParam(value = "industryFilter9", defaultValue = "") String industryFilter9,//
			HttpSession session, ModelMap model) {
		setupNYTNews(model);
		if (session.getAttribute("HF") == null) {
			return "login";
		}

		final Paging paging = new Paging();
		paging.setCount(10);
		paging.setCurrentPage(Integer.parseInt(page));
		if (paging.getCurrentPage() <= 0) {
			paging.setCurrentPage(1);
		}

		HedgeFund hf = (HedgeFund) session.getAttribute("HF");
		model.addAttribute("user", hf);

		int p = paging.getCount() * (paging.getCurrentPage() - 1);
		final List<Student> students = new ArrayList<Student>();

		// filter info
		final String[] schoolFilters = new String[] { schoolFilter1,//
				schoolFilter2,//
				schoolFilter3,//
				schoolFilter4,//
				schoolFilter5,//
				schoolFilter6,//
		};
		final String[] industryFilters = new String[] { industryFilter1,//
				industryFilter2,//
				industryFilter3,//
				industryFilter4,//
				industryFilter5,//
				industryFilter6,//
				industryFilter7,//
				industryFilter8,//
				industryFilter9,//
		};

		StringBuilder buffSchoolFilter = new StringBuilder();
		for (String filter : schoolFilters) {
			if (!filter.isEmpty()) {
				if (buffSchoolFilter.length() == 0) {
					buffSchoolFilter.append("(");
				} else {
					buffSchoolFilter.append(" or ");
				}
				buffSchoolFilter.append("(college like '%").append(filter)
						.append("%' or graduateSchool like '%").append(filter)
						.append("%')");
			}
		}
		if (buffSchoolFilter.length() > 0) {
			buffSchoolFilter.append(")");
		}

		StringBuilder buffIndustryFilter = new StringBuilder();
		for (String filter : industryFilters) {
			if (!filter.isEmpty()) {
				if (buffIndustryFilter.length() == 0) {
					buffIndustryFilter.append("(");
				} else {
					buffIndustryFilter.append(" or ");
				}
				buffIndustryFilter.append("employerIndustry like '%")
						.append(filter).append("%'");
			}
		}
		if (buffIndustryFilter.length() > 0) {
			buffIndustryFilter.append(")");
		}

		String filterSql = "";
		if (buffSchoolFilter.length() > 0 && buffIndustryFilter.length() > 0) {
			filterSql = " and " + buffSchoolFilter.toString() + " and "
					+ buffIndustryFilter.toString();
		} else if (buffSchoolFilter.length() > 0) {
			filterSql = " and " + buffSchoolFilter.toString();
		} else if (buffIndustryFilter.length() > 0) {
			filterSql = " and " + buffIndustryFilter.toString();
		}

		// search
		String sql = null;
		String sql2 = null;
		if (keyWords == null || keyWords.length() == 0) {
			sql = "select * from ("
					+ " select row_number() over() as rownum, tmp2.* from ("
					+ "select * from student where availability<>'Not be seen'"
					+ filterSql + " order by firstName) as tmp2" + " ) as tmp"
					+ " where rownum >= ? and rownum < ? order by firstName";
			sql2 = "select count(*) as totalcount from student where availability<>'Not be seen'"
					+ filterSql;
		} else {
			String words[] = keyWords.split("\\s+");
			StringBuilder sqlpart = new StringBuilder(" where ");
			for (int i = 0; i < words.length; ++i) {
				if (i != 0) {
					sqlpart.append("and (");
				} else {
					sqlpart.append("(");
				}
				sqlpart.append("firstName like '%")
						.append(words[i].replace("'", "''"))
						.append("%' or lastName like '%")
						.append(words[i].replace("'", "''")).append("%') ");
			}
			sql = "select * from ("
					+ " select row_number() over() as rownum, tmp2.* from ("
					+ "select * from student where availability<>'Not be seen'"
					+ filterSql + " order by firstName) as tmp2"
					+ sqlpart.toString() + " ) as tmp"
					+ " where rownum >= ? and rownum < ? order by firstName";
			sql2 = "select count(*) as totalcount from student"
					+ sqlpart.toString() + " and availability<>'Not be seen'"
					+ filterSql;
		}

		dbmanager.doQuery(new IGetFromResultSet() {
			public void getFromResultSet(ResultSet rs) throws SQLException {
				while (rs.next()) {
					Student student = new Student();
					student.getFromResultSet(rs);
					students.add(student);
				}
			}
		}, sql, p, p + 10);
		model.addAttribute("students", students);

		dbmanager.doQuery(new IGetFromResultSet() {
			public void getFromResultSet(ResultSet rs) throws SQLException {
				while (rs.next()) {
					paging.setTotal(rs.getInt("totalcount"));
				}
			}
		}, sql2);

		List<Paging> pagings = paging.createPagingList();
		model.addAttribute("page", page);
		model.addAttribute("pagings", pagings);
		model.addAttribute("needPaging", !pagings.isEmpty());
		model.addAttribute("currentIndex", paging.getCurrentPage());
		model.addAttribute("previousIndex", paging.getCurrentPage() - 1);
		model.addAttribute("nextIndex", paging.getCurrentPage() + 1);
		model.addAttribute("needPrevious",
				!pagings.isEmpty() && paging.getCurrentPage() > 1);
		model.addAttribute(
				"needNext",
				!pagings.isEmpty()
						&& paging.getCurrentPage() < paging.getPageCount());
		model.addAttribute("searchWords", keyWords);
		model.addAttribute("schoolFilter1", schoolFilter1);
		model.addAttribute("schoolFilter2", schoolFilter2);
		model.addAttribute("schoolFilter3", schoolFilter3);
		model.addAttribute("schoolFilter4", schoolFilter4);
		model.addAttribute("schoolFilter5", schoolFilter5);
		model.addAttribute("schoolFilter6", schoolFilter6);
		model.addAttribute("industryFilter1", industryFilter1);
		model.addAttribute("industryFilter2", industryFilter2);
		model.addAttribute("industryFilter3", industryFilter3);
		model.addAttribute("industryFilter4", industryFilter4);
		model.addAttribute("industryFilter5", industryFilter5);
		model.addAttribute("industryFilter6", industryFilter6);
		model.addAttribute("industryFilter7", industryFilter7);
		model.addAttribute("industryFilter8", industryFilter8);
		model.addAttribute("industryFilter9", industryFilter9);

		return "hf/studentProfile";
	}

	@RequestMapping("profile")
	public String viewProfile(//
			@RequestParam(value = "email", defaultValue = "") String email,//
			HttpSession session, ModelMap model) {
		setupNYTNews(model);
		if (session.getAttribute("STUDENT") == null
				&& session.getAttribute("HF") == null) {
			return "login";
		}
		// display in student pages.
		if (session.getAttribute("STUDENT") != null) {
			Student student = (Student) session.getAttribute("STUDENT");
			model.addAttribute("user", student);
			if (!"".equals(email)) {
				student = new Student();
				dbmanager.queryFirst(student,
						"select * from student where lower(email)=?", email.toLowerCase());
			}
			// career interest
			updateStudentCareerInterest(student);

			model.addAttribute("student", student);
			return "student/profile";
		}
		// display in hf pages.
		if ("".equals(email)) {
			return "hf/home";
		}
		Student student = new Student();
		dbmanager.queryFirst(student, "select * from student where lower(email)=?",
				email.toLowerCase());
		if (student.getEmail() == null || "".equals(student.getEmail())) {
			return "hf/home";
		}
		// career interest
		updateStudentCareerInterest(student);
		HedgeFund hf = (HedgeFund) session.getAttribute("HF");
		model.addAttribute("user", hf);
		model.addAttribute("student", student);
		return "hf/studentProfileDetail";
	}

	private void updateStudentCareerInterest(final Student student) {
		student.getCareerInterests().clear();
		dbmanager.doQuery(new IGetFromResultSet() {
			public void getFromResultSet(ResultSet rs) throws SQLException {
				while (rs.next()) {
					CareerInterest ci = new CareerInterest();
					ci.getFromResultSet(rs);
					student.getCareerInterests().add(ci);
				}
			}
		}, "select * from careerinterest where lower(studentEmail)=?",
				student.getEmail().toLowerCase());
	}

	@RequestMapping("register")
	public String register(HttpSession session, ModelMap model) {
		setupNYTNews(model);
		session.removeAttribute("STUDENT");
		Student student = new Student();
		student.setFirstName("");
		student.setLastName("");
		student.setEmail("");
		student.setPassword("");
		student.setCollege("");
		student.setGraduateSchool("");
		student.setYearOfGraduation("");
		student.setRoleOfInterest("");
		student.setLocationPreference("");
		student.setCurrentEmployer("");
		student.setJobTitle("");
		student.setFormerEmployer("");
		student.setEmployerIndustry("");
		student.setAvailability("");
		student.setPhotoId("");
		student.setInvestingBackground("");
		student.setCvId("");
		student.setCvFileName("");
		model.addAttribute("student", student);
		model.addAttribute("register", true);
		return "student/register";
	}

	@RequestMapping("editProfile")
	public String editProfile(HttpSession session, ModelMap model) {
		setupNYTNews(model);
		if (session.getAttribute("STUDENT") == null) {
			Student student = new Student();
			student.setFirstName("");
			student.setLastName("");
			student.setEmail("");
			student.setPassword("");
			student.setCollege("");
			student.setGraduateSchool("");
			student.setYearOfGraduation("");
			student.setRoleOfInterest("");
			student.setLocationPreference("");
			student.setCurrentEmployer("");
			student.setJobTitle("");
			student.setFormerEmployer("");
			student.setEmployerIndustry("");
			student.setAvailability("");
			student.setPhotoId("");
			student.setInvestingBackground("");
			student.setCvId("");
			student.setCvFileName("");
			model.addAttribute("student", student);
			model.addAttribute("register", true);
		} else {
			model.addAttribute("student", session.getAttribute("STUDENT"));
			model.addAttribute("register", false);
		}
		return "student/register";
	}

	@RequestMapping("profile/edit")
	public String eidtProfile(//
			@RequestParam("firstName") String firstName,//
			@RequestParam("lastName") String lastName,//
			@RequestParam("email") String email,//
			@RequestParam("password") String password,//
			@RequestParam("college") String college,//
			@RequestParam("graduateSchool") String graduateSchool,//
			@RequestParam("yearOfGraduation") String yearOfGraduation,//
			@RequestParam("roleOfInterest") String roleOfInterest,//
			@RequestParam("locationPreference") String locationPreference,//
			@RequestParam("currentEmployer") String currentEmployer,//
			@RequestParam("jobTitle") String jobTitle,//
			@RequestParam("formerEmployer") String formerEmployer,//
			@RequestParam("employerIndustry") String employerIndustry,//
			@RequestParam("availability") String availability,//
			@RequestParam("photoId") String photoId,//
			@RequestParam("investingBackground") String investingBackground,//
			@RequestParam("cvId") String cvId,//
			@RequestParam("cvFileName") String cvFileName,//
			@RequestParam("careerInterests1") String careerInterests1,//
			@RequestParam("careerInterests2") String careerInterests2,//
			@RequestParam("careerInterests3") String careerInterests3,//
			@RequestParam("careerInterests4") String careerInterests4,//
			HttpSession session, ModelMap model) {

		JsonData json = new JsonData();
		model.addAttribute("jsonData", json);

		Student student = null;

		boolean insert = session.getAttribute("STUDENT") == null;

		if (insert) {
			student = new Student();
			dbmanager.queryFirst(student,
					"select * from student where lower(email)=?", email.toLowerCase());
			if (email.equalsIgnoreCase(student.getEmail())) {
				json.setErrorMessage("Email address:" + email
						+ " is already registered.");
				return "json";
			}
			student = new Student();
		} else {
			student = (Student) session.getAttribute("STUDENT");
		}
		student.setFirstName(firstName);
		student.setLastName(lastName);
		student.setEmail(email);
		student.setPassword(password);
		student.setCollege(college);
		student.setGraduateSchool(graduateSchool);
		student.setYearOfGraduation(yearOfGraduation);
		student.setRoleOfInterest(roleOfInterest);
		student.setLocationPreference(locationPreference);
		student.setCurrentEmployer(currentEmployer);
		student.setJobTitle(jobTitle);
		student.setFormerEmployer(formerEmployer);
		student.setEmployerIndustry(employerIndustry);
		student.setAvailability(availability);
		student.setPhotoId(photoId);
		student.setInvestingBackground(investingBackground);
		student.setCvId(cvId);
		student.setCvFileName(cvFileName);
		if (insert) {
			json.setSucceed(dbmanager.execute(student.toInsertString()));
		} else {
			json.setSucceed(dbmanager.execute(student.toUpdateString()));
		}

		if (json.isSucceed()) {
			session.setAttribute("STUDENT", student);
		} else {
			json.setErrorMessage("Failed to update user profile");
			return "json";
		}

		student.getCareerInterests().clear();
		String[] careerInterests = new String[] {//
		careerInterests1, //
				careerInterests2, //
				careerInterests3, //
				careerInterests4, //
		};
		dbmanager.execute("delete from careerinterest where lower(studentEmail)='"
				+ student.getEmail().toLowerCase() + "'");
		for (String cistr : careerInterests) {
			if (cistr.trim().length() > 0) {
				CareerInterest ci = new CareerInterest();
				ci.setCareerInterest(cistr);
				ci.setStudentEmail(student.getEmail());
				dbmanager.execute(ci.toInsertString());
				student.getCareerInterests().add(ci);
			}
		}

		return "json";
	}
}
